package com.example.dao;

import com.example.entity.rental;
import com.example.entity.transaction;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class rentalDaoImpl implements rentalDao {

    private final Connection connection;

    public rentalDaoImpl() throws SQLException {
        this.connection = getConnection();
    }

    //插入方法
    public boolean insertRentalData(rental item) throws SQLException {
        String sql = "INSERT INTO rental (name, price, volume, date) VALUES (?, ?, ?, ?)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, item.getName());
            stmt.setBigDecimal(2, item.getPrice());
            stmt.setInt(3, item.getVolume());
            stmt.setDate(4, new java.sql.Date(item.getDate().getTime()));
            return stmt.executeUpdate() > 0;
        }
    }


    //存在判断
    @Override
    public boolean existsByName(String name) throws SQLException {
        String sql = "SELECT COUNT(*) FROM rental WHERE name = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, name);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0; // 如果 count > 0 返回 true
                }
            }
        }
        return false;
    }



    @Override
    public List<rental> getTodayByAll() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE date = CURDATE() ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getYesterdayByAll() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getThisWeekByAll() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE YEARWEEK(date, 1) = YEARWEEK(CURDATE(), 1) ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getThisMonthByAll() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getTodayBy0To100() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE date = CURDATE() AND price BETWEEN 0 AND 100 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getYesterdayBy0To100() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND price BETWEEN 0 AND 100 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getThisWeekBy0To100() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE YEARWEEK(date, 1) = YEARWEEK(CURDATE(), 1) AND price BETWEEN 0 AND 100 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getThisMonthBy0To100() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) AND price BETWEEN 0 AND 100 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getTodayBy100To500() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE date = CURDATE() AND price BETWEEN 100 AND 500 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getYesterdayBy100To500() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND price BETWEEN 100 AND 500 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getThisWeekBy100To500() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE YEARWEEK(date, 1) = YEARWEEK(CURDATE(), 1) AND price BETWEEN 100 AND 500 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getThisMonthBy100To500() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) AND price BETWEEN 100 AND 500 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getTodayBy500To1000() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE date = CURDATE() AND price BETWEEN 500 AND 1000 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getYesterdayBy500To1000() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND price BETWEEN 500 AND 1000 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getThisWeekBy500To1000() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE YEARWEEK(date, 1) = YEARWEEK(CURDATE(), 1) AND price BETWEEN 500 AND 1000 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getThisMonthBy500To1000() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) AND price BETWEEN 500 AND 1000 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getTodayByAbove1000() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE date = CURDATE() AND price > 1000 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getYesterdayByAbove1000() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND price > 1000 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getThisWeekByAbove1000() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE YEARWEEK(date, 1) = YEARWEEK(CURDATE(), 1) AND price > 1000 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    @Override
    public List<rental> getThisMonthByAbove1000() throws SQLException {
        String sql = "SELECT name, price, volume, date FROM rental WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) AND price > 1000 ORDER BY volume DESC LIMIT 10";
        return queryList(sql);
    }

    // 公共查询方法
    private List<rental> queryList(String sql) throws SQLException {
        List<rental> list = new ArrayList<>();
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                rental item = new rental();
                item.setName(rs.getString("name"));
                item.setPrice(rs.getBigDecimal("price"));
                item.setVolume(rs.getInt("volume"));
                item.setDate(rs.getDate("date"));
                list.add(item);
            }
        }
        return list;
    }
}
